package sv.gob.mh.dgii.dao.hibernate;

import java.io.Serializable;

import java.lang.reflect.Method;
import java.math.BigDecimal;
import java.sql.CallableStatement;
import java.sql.Connection;
import java.sql.ResultSet;
import java.sql.SQLException;
import java.sql.Types;
import java.util.ArrayList;
import java.util.Calendar;
import java.util.Collection;
import java.util.Date;
import java.util.HashMap;
import java.util.Iterator;
import java.util.List;
import java.util.Map;
import org.apache.commons.beanutils.BasicDynaBean;
import org.apache.commons.beanutils.BasicDynaClass;
import org.apache.commons.beanutils.DynaBean;
import org.apache.commons.beanutils.PropertyUtils;
import org.hibernate.Criteria;
import org.hibernate.HibernateException;
import org.hibernate.Query;
import org.hibernate.Session;
import org.hibernate.SessionFactory;
import org.hibernate.criterion.Criterion;
import org.hibernate.criterion.DetachedCriteria;
import org.hibernate.criterion.Example;
import org.hibernate.criterion.MatchMode;
import org.hibernate.criterion.Order;
import org.hibernate.criterion.Projection;
import org.hibernate.criterion.ProjectionList;
import org.hibernate.criterion.Projections;
import org.hibernate.criterion.Property;
import org.hibernate.criterion.Restrictions;
import org.hibernate.internal.SQLQueryImpl;
import org.hibernate.jdbc.Work;
import org.hibernate.metadata.ClassMetadata;
import org.hibernate.type.Type;
import org.springframework.beans.BeanUtils;
import org.springframework.beans.factory.annotation.Autowired;
import org.springframework.orm.ObjectRetrievalFailureException;
import sv.gob.mh.dgii.common.BeanHelper;
import sv.gob.mh.dgii.common.Constants;
import sv.gob.mh.dgii.common.PagedResult;
import sv.gob.mh.dgii.common.Utils;
import sv.gob.mh.dgii.hibernate.FinderArgumentTypeFactory;
import sv.gob.mh.dgii.hibernate.SimpleFinderArgumentTypeFactory;
import sv.gob.mh.dgii.jdbc.JdbcHelper;
import sv.gob.mh.dgii.jdbc.NamedParameterStatement;
import sv.gob.mh.dgii.jdbc.Oracle10gRowSetDynaClass;
import sv.gob.mh.dgii.xml.XmlResultSet;

public class HibernateDaoImpl implements Dao {

    @Autowired
    private SessionFactory sessionFactory;
    @SuppressWarnings("rawtypes")
    protected Class clazz;
    private FinderArgumentTypeFactory argumentTypeFactory = new SimpleFinderArgumentTypeFactory();
    private JdbcHelper jdbcHelper;

    public HibernateDaoImpl(@SuppressWarnings("rawtypes") Class clazz) {
        this.clazz = clazz;
    }

    public void saveOrUpdate(Object obj){
        sessionFactory.getCurrentSession().saveOrUpdate(obj);
    }
    public void save(Object obj) {
        sessionFactory.getCurrentSession().save(obj);
    }

    public void update(Object obj) {
        sessionFactory.getCurrentSession().merge(obj);
    }

    @SuppressWarnings("rawtypes")
    public List find(Object example) {
        return getByExample(example);
    }

    public void remove(Object obj) {
        Object id = getObjectId(obj);
        removeWithId((Serializable) id);
    }

    public void removeWithId(Serializable id) {
        sessionFactory.getCurrentSession().delete(getObjectById(id));
    }

    public Object findById(Serializable id) {
        return findById(getClazz(), id);
    }

    public Object findById(Class c, Serializable id) {
        Object obj = sessionFactory.getCurrentSession().get(c, id);
        //Agrego exception para que desde el controlador saber cuando un objeto no existe.
        if (obj == null) {
            throw new ObjectRetrievalFailureException(clazz, id);
        }
        return obj;
    }

    public List findAll() {
        List ret = null;
        Session session = sessionFactory.getCurrentSession();
        Query q = session.createQuery("from " + getClazz().getName());
        if (q != null) {
            ret = q.list();
        }
        return ret;
    }

    public Object findByKey(Object obj) {
        Object id = getObjectId(obj);
        return getObjectById(id);
    }

    @SuppressWarnings("rawtypes")
    public List getAll() {
        DetachedCriteria dc = DetachedCriteria.forClass(getClazz());
        return executeCriteria(dc);
    }

    //Cuenta el numero total de registros de una tabla (sin filtros)
    public Number getAllCount() {
        DetachedCriteria dc = DetachedCriteria.forClass(getClazz());
        Criteria c = dc.getExecutableCriteria(sessionFactory.getCurrentSession());
        c.setProjection(Projections.rowCount());
        Number count = (Number) c.uniqueResult();
        //c.setProjection(proj);
        //c.setResultTransformer(transformer);
        return count;
    }

    //Con llaves compuestas a un no ha sido probado
    //Pagina cuando devolvemos <b>todos los registros</b>, aun no hay filtros de propiedades
    public List getPage(int page, int pageSize, String sortProperty, String sortDirection) {
        DetachedCriteria dc = DetachedCriteria.forClass(getClazz());
        Criteria c = dc.getExecutableCriteria(sessionFactory.getCurrentSession());
        ClassMetadata meta = getSessionFactory().getClassMetadata(getClazz());
        c.setFirstResult((page - 1) * pageSize);
        c.setMaxResults(pageSize);

        if (Utils.isNullOrEmpty(sortProperty)) {
            sortProperty = meta.getIdentifierPropertyName();
        }
        if (Utils.isNullOrEmpty(sortDirection)) {
            sortDirection = "asc";
        }

        if ("desc".equalsIgnoreCase(sortDirection)) {
            c.addOrder(Order.desc(sortProperty));
        } else {
            c.addOrder(Order.asc(sortProperty));
        }
        return c.list();
    }

    @Override
    public PagedResult getPagedResult(int page, int pageSize, String sortProperty, String sortDirection) {
        return getPage(page, pageSize, sortProperty, sortDirection, null);
    }

    @Override
    public PagedResult getPage(int page, int pageSize, String sortProperty, String sortDirection, Object sample) {
        PagedResult ret = new PagedResult();
        if (sample == null) {
            sample = new Object();
        }
        DetachedCriteria dc = createCriteria(sample, true, true, MatchMode.ANYWHERE);
        Criteria c = dc.getExecutableCriteria(sessionFactory.getCurrentSession());
        ClassMetadata meta = getSessionFactory().getClassMetadata(getClazz());
        // count
        c.setProjection(Projections.rowCount());
        ret.setCounter(((Number) c.uniqueResult()).intValue());

        // all filtered
        c.setProjection(null);
        if (page > 0 && pageSize > 0) {
            c.setFirstResult((page - 1) * pageSize);
            c.setMaxResults(pageSize);
        }

        if (Utils.isNullOrEmpty(sortProperty)) {
            sortProperty = meta.getIdentifierPropertyName();
        }
        if (Utils.isNullOrEmpty(sortDirection)) {
            sortDirection = "asc";
        }
        if ("desc".equalsIgnoreCase(sortDirection)) {
            c.addOrder(Order.desc(sortProperty));
        } else {
            c.addOrder(Order.asc(sortProperty));
        }
        ret.setList(c.list());
        return ret;
    }

    @Override
    public PagedResult getPage(int page, int pageSize, String sortProperty, String sortDirection, List criterions) {
        PagedResult ret = new PagedResult();
        List<String> projectionsDistinct = new ArrayList<String>();
        ProjectionList projectionsCount = Projections.projectionList().create();
        DetachedCriteria dc = DetachedCriteria.forClass(getClazz());
        Criteria c = dc.getExecutableCriteria(sessionFactory.getCurrentSession());
        ClassMetadata meta = getSessionFactory().getClassMetadata(getClazz());
        if (criterions != null && criterions.size() > 0) {
            for (Object o : criterions) {
                if (o instanceof Criterion) {
                    Criterion restriction;
                    restriction = (Criterion) o;
                    c.add(restriction);
                } else if (o instanceof Map) {
                    Map<String, Object> map = (Map<String, Object>) o;
                    for (Map.Entry<String, Object> entry : map.entrySet()) {
                        String key = entry.getKey();
                        if (!Utils.isNullOrEmpty(key)) {
                            if (key.toLowerCase().contains("sql")) {
                                String strValue = (String) entry.getValue();
                                if (strValue != null && !strValue.trim().equals("")) {
                                    c.add(Restrictions.sqlRestriction(strValue));
                                }
                            } else if (entry.getValue() != null) {
                                if (key.contains("<>") || key.contains("!=")) {
                                    // key= "campo<>", "<>campo", "campo!=", "!=campo";
                                    String strKey = key.replaceAll("<>", "").replaceAll("!=", "");
                                    c.add(Restrictions.ne(strKey, entry.getValue()));
                                } else if (entry.getValue() instanceof String) {
                                    String strValue = (String) entry.getValue();
                                    if (strValue != null && !Utils.isNullOrEmpty(strValue)) {
                                        strValue = strValue.trim();
                                        if (strValue.trim().toLowerCase().equals("null")) {
                                            c.add(Restrictions.isNull(key));
                                        } else if (strValue.toLowerCase().contains("distinct") || key.toLowerCase().contains("distinct")) {
                                            projectionsDistinct.add(key.replaceAll("distinct", "").trim());
                                        } else if (strValue.contains("%")) {
                                            if (strValue.trim().startsWith("%") && strValue.trim().endsWith("%")) {
                                                c.add(Restrictions.ilike(key, strValue.replaceAll("%", ""), MatchMode.ANYWHERE));
                                            } else if (strValue.trim().startsWith("%")) {
                                                c.add(Restrictions.ilike(key, strValue.replaceAll("%", ""), MatchMode.END));
                                            } else {
                                                c.add(Restrictions.ilike(key, strValue.replaceAll("%", ""), MatchMode.START));
                                            }
                                        } else {
                                            c.add(Restrictions.or(Restrictions.eq(key, strValue.toLowerCase()), Restrictions.eq(key, strValue.toUpperCase())));
                                        }
                                    }
                                } else if (key.contains("between") && entry.getValue() instanceof Object[]) {
                                    Object[] params = (Object[]) entry.getValue();
                                    if (params.length == 3) {
                                        String field = (String) params[0];
                                        Object firstComparator = params[1];
                                        Object secondComparator = params[2];
                                        c.add(Restrictions.between(field, firstComparator, secondComparator));
                                    }
                                } else if (!key.contains("between") && entry.getValue() instanceof String[]) {
                                    c.add(Restrictions.in(key, (String[]) entry.getValue()));
                                } else if (entry.getValue() instanceof Date[]) {
                                    Date[] periodo = (Date[]) entry.getValue();
                                    if (periodo.length == 2) {
                                        c.add(Restrictions.between(key, periodo[0], periodo[1]));
                                    }
                                } else {
                                    c.add(Restrictions.eq(entry.getKey(), entry.getValue()));
                                }
                            } else {
                                c.add(Restrictions.isNull(key));
                            }
                        }
                    }
                }
            }
        }
        if (projectionsDistinct.size() > 0) {
            for (String key : projectionsDistinct) {
                projectionsCount.add(Projections.countDistinct(key));
            }
        } else {
            projectionsCount.add(Projections.rowCount());
        }
        c.setProjection(projectionsCount);
        ret.setCounter(((Number) c.uniqueResult()).intValue());
        if (projectionsDistinct.size() > 0) {
            for (String key : projectionsDistinct) {
                c.setProjection(Projections.distinct(Property.forName(key)));
            }
        } else {
            c.setProjection(null);
        }
        if (page > 0 && pageSize > 0) {
            c.setFirstResult((page - 1) * pageSize);
            c.setMaxResults(pageSize);
        }

        if (Utils.isNullOrEmpty(sortProperty)) {
            sortProperty = meta.getIdentifierPropertyName();
        }
        if (Utils.isNullOrEmpty(sortDirection)) {
            sortDirection = "asc";
        }
        if ("desc".equalsIgnoreCase(sortDirection)) {
            c.addOrder(Order.desc(sortProperty));
        } else {
            c.addOrder(Order.asc(sortProperty));
        }
        ret.setList(c.list());
        return ret;
    }

    @SuppressWarnings("rawtypes")
    public List getByExample(Object example) {
        DetachedCriteria dc = createCriteria(example, true, true,
                MatchMode.ANYWHERE);
        return executeCriteria(dc);
    }

    public void synchronize() {
        sessionFactory.getCurrentSession().flush();

    }

    public Object getObjectById(Object id) {
        Object obj = sessionFactory.getCurrentSession().get(clazz, (Serializable) id);
        if (obj == null) {
            throw new ObjectRetrievalFailureException(clazz, id);
        }
        return obj;
    }

    /**
     * obtiene el id del objeto segun la metadata de hibernate
     *
     * @param obj
     * @return
     */
    public Serializable getObjectId(Object obj) {
        ClassMetadata metadata = getMetadata();
        String idProp = metadata.getIdentifierPropertyName();
        Object id = null;
        try {
            char[] chars = idProp.toUpperCase().toCharArray();
            String methodName = "get" + chars[0]
                    + idProp.substring(1, idProp.length());
            Method method = obj.getClass()
                    .getMethod(methodName, (Class[]) null);
            id = method.invoke(obj, (Object[]) null);
        } catch (Exception e) {
            e.printStackTrace();
            return (Serializable) obj;
        }
        return (Serializable) id;
    }

    public List executeHql(String hql) {
        return getSessionFactory().getCurrentSession()
                .createQuery(hql).setMaxResults(Constants.MAX_RESULTS).list();
    }

    public List executeHql(String hql, Object[] params) {
        Query query = getSessionFactory()
                .getCurrentSession().createQuery(hql);

        for (int i = 0; i < params.length; i++) {
            Object param = params[i];
            query.setParameter(i, param);
        }

        return query.setMaxResults(Constants.MAX_RESULTS).list();
    }

    /**
     * ejecuta el Detachde Criteria utilizando el template de spring, pasando
     * una cota maxima de MAX_RESULTS items para el query
     *
     * @param dc
     * @return
     */
    @SuppressWarnings("rawtypes")
    public List executeCriteria(DetachedCriteria dc) {
        return dc.getExecutableCriteria(sessionFactory.getCurrentSession())
                .setFirstResult(Constants.INIT_RESULTS)
                .setMaxResults(Constants.MAX_RESULTS).list();
    }

    public Object executeCriteriaUniqueResult(DetachedCriteria dc) {
        return dc.getExecutableCriteria(sessionFactory.getCurrentSession())
                .setMaxResults(1)
                .uniqueResult();
    }

    /**
     * Este metodo carece de la capacidad para crear un example adecuado cuando
     * se especifica una parte del id por lo que no puede generar el sql exacto
     * cuando se especifican campos de la tabla y una parte del id. Para ver una
     * implementacion adecuada de este metodo ver
     *
     * @see HibernateDaoCriteriaExtended#createCriteria(Object, boolean,
     * boolean, MatchMode)
     *
     * @param example
     * @param id
     * @return Criteria a criteria using an Example + id strategy, infering the
     * key values
     */
    @SuppressWarnings("rawtypes")
    public DetachedCriteria createCriteria(Object example, boolean ignoreCase,
            boolean ignoreZeroes, MatchMode mode) {

        Object id = getObjectId(example);
        if (ignoreZeroes && id instanceof Number) {
            Number num = (Number) id;
            if (num.doubleValue() == 0) {
                id = null;
            }
        }

        BeanHelper.setEmptyStringsAndZeroNumbersToNull(example);
        Example xample = Example.create(example);

        if (ignoreCase) {
            xample.ignoreCase();
        }
        if (ignoreZeroes) {
            xample.excludeZeroes();
        }
        if (!Utils.isNullOrEmpty(mode)) {
            xample.enableLike(mode);
        }

        xample.ignoreCase().excludeZeroes();
        DetachedCriteria dc = DetachedCriteria.forClass(getClazz());
        if (!Utils.isNullOrEmpty(id)) {
            if (BeanUtils.isSimpleProperty(id.getClass())) {

                dc.add(Restrictions.idEq(id));
            } else {
                String idProp = getMetadata().getIdentifierPropertyName();
                Map props = BeanHelper.getProperties(id);
                Object objValue;
                String key;
                for (Iterator iter = props.keySet().iterator(); iter.hasNext();) {
                    key = (String) iter.next();
                    objValue = props.get(key);
                    if (!Utils.isNullOrEmpty(objValue)) {

                        dc.add(Restrictions.eq(idProp + "." + key, objValue));
                    }
                }
            }
        } else {
            dc.add(xample);
        }
        return dc;
    }

    public List executeNamedQuery(String queryName, Object[] queryArgs) {
        final Query namedQuery = sessionFactory.getCurrentSession().getNamedQuery(queryName.trim());

        String[] namedParameters = namedQuery.getNamedParameters();
        if (namedParameters.length == 0) {
            setPositionalParams(queryArgs, namedQuery);
        } else {
            setNamedParams(namedParameters, queryArgs, namedQuery);
        }
        if (namedQuery instanceof SQLQueryImpl) {
            return executeSqlQuery(namedQuery, queryArgs);
        }
        List lst = namedQuery.list();
        return (List) lst;
    }

    private void setPositionalParams(Object[] queryArgs, Query namedQuery) {
        if (queryArgs != null) {
            for (int i = 0; i < queryArgs.length; i++) {
                Object arg = queryArgs[i];
                Type argType = argumentTypeFactory.getArgumentType(arg);
                if (argType != null) {
                    namedQuery.setParameter(i, arg, argType);
                } else {
                    namedQuery.setParameter(i, arg);
                }
            }
        }
    }

    private void setNamedParams(String[] namedParameters, Object[] queryArgs,
            Query namedQuery) {
        /*
         * el namedParameters retorna los nombres de los parametros en orden
         * inverso, asi que haremos el match del ultimo con el primero
         */

        int npl = 0;
        if (namedParameters != null) {
            npl = namedParameters.length - 1;
        }

        if (queryArgs != null) {
            for (int i = 0; i < queryArgs.length; i++) {
                Object arg = queryArgs[i];
                Type argType = argumentTypeFactory.getArgumentType(arg);
                if (argType != null) {
                    namedQuery.setParameter(namedParameters[npl - i], arg,
                            argType);
                } else {
                    if (arg instanceof Collection) {
                        namedQuery.setParameterList(namedParameters[npl - i],
                                (Collection) arg);
                    } else {
                        namedQuery.setParameter(namedParameters[npl - i], arg);
                    }
                }
            }
        }
    }

    private List<DynaBean> executeSqlQuery(Query query, Object[] params) {
        final Query queryFinal = query;
        final Object[] paramsFinal = params;
        final Object[] dynaBeanFinal = new Object[1];
        try {
            sessionFactory.getCurrentSession().doWork(new Work() {
                public void execute(Connection con) throws SQLException {
                    try {
                        NamedParameterStatement nps = new NamedParameterStatement(
                                con, Constants.SELECT_ALL
                                + queryFinal.getQueryString()
                                + Constants.WHERE_MAX_ALL);
                        // crear mapa con parametro + nombre
                        String[] names = queryFinal.getNamedParameters();
                        Map<String, Object> pmap = new HashMap<String, Object>();
                        for (int i = 0; i < names.length; i++) {
                            pmap.put(names[i],
                                    paramsFinal[(paramsFinal.length - 1) - i]);
                        }
                        nps.setParameters(pmap);
                        dynaBeanFinal[0] = JdbcHelper.getRows(nps);
                        nps = null;
                    } catch (SQLException e) {
                        throw new HibernateException(e);

                    }
                }
            });

            return (List<DynaBean>) dynaBeanFinal[0];
        } catch (Exception e) {
            throw new HibernateException(e);

        }

    }

    public List executeSqlNamedQuery(String queryName,
            Map<String, Object> queryArgs) {
        final Query namedQuery = sessionFactory.getCurrentSession().getNamedQuery(queryName.trim());
        List lst = executeSqlQuery(namedQuery, queryArgs);
        return lst;
    }

    public List executeSqlQuery(Query query, Map<String, Object> params) {
        final Query queryFinal = query;
        final Map<String, Object> paramsFinal = params;
        final Object[] dynaBeanFinal = new Object[1];

        sessionFactory.getCurrentSession().doWork(new Work() {
            public void execute(Connection con) throws SQLException {
                try {
                    NamedParameterStatement nps = new NamedParameterStatement(
                            con, Constants.SELECT_ALL
                            + queryFinal.getQueryString()
                            + Constants.WHERE_MAX_ALL);
                    if (paramsFinal != null) {
                        nps.setParameters(paramsFinal);
                    }

                    dynaBeanFinal[0] = getRows(nps);
                } catch (SQLException e) {
                    throw new HibernateException(e);

                }
            }
        });

        return (List) dynaBeanFinal[0];

    }

    /**
     * @see @param cadena sql y arreglo de parametros
     * @return nothing,
     */
    public void executeProcedure(String sql, Object[] params) throws Exception {

        final String sqlToWork = sql;
        final Object[] paramsToWork = params;

        sessionFactory.getCurrentSession().doWork(new Work() {
            public void execute(Connection conn) {
                CallableStatement cstmt = null;

                try {
                    cstmt = conn.prepareCall(sqlToWork);
                    if (paramsToWork != null) {
                        for (int i = 0; i < paramsToWork.length; i++) {
                            Object param = paramsToWork[i];
                            cstmt.setObject(i + 1, param, resolveType(param));
                        }
                    }
                    cstmt.execute();
                    cstmt.close();
                } catch (SQLException e) {
                    try {
                        cstmt.close();
                    } catch (SQLException ex) {
                        throw new HibernateException(e);
                    }
                    throw new HibernateException(e);

                } finally {
                    cstmt = null;

                }

            }
        });

    }

    public Object[] executeProcedure(String sql, Object[] inParams,
            Object[] outParams) throws Exception {

        final String sqlToWork = sql;
        final Object[] paramsToWork = inParams;
        final Object[] paramsOutToWork = outParams;
        final Object[] result = new Object[outParams.length];

        sessionFactory.getCurrentSession().doWork(new Work() {
            @Override
            public void execute(Connection conn) {
                CallableStatement cstmt = null;

                try {
                    cstmt = conn.prepareCall(sqlToWork);
                    if (paramsToWork != null) {
                        for (int i = 0; i < paramsToWork.length; i++) {
                            Object param = paramsToWork[i];
                            cstmt.setObject(i + 1, param, resolveType(param));
                        }
                    }

                    int lbegin = paramsToWork.length;

                    if (paramsOutToWork != null) {
                        for (int j = 0; j < paramsOutToWork.length; j++) {
                            Object param = paramsOutToWork[j];
                            cstmt.registerOutParameter(lbegin + (j + 1),
                                    resolveType(param));
                        }
                    }
                    cstmt.execute();
                    //Llenado del arreglo de Objects de Salida					
                    for (int x = 0; x < result.length; x++) {
                        Object m = cstmt.getObject(lbegin + (x + 1));
                        result[x] = m;
                    }

                } catch (SQLException e) {
                    try {
                        cstmt.close();
                    } catch (SQLException ex) {
                        throw new HibernateException(e);
                    }

                    throw new HibernateException(e);

                } finally {
                    cstmt = null;

                }

            }
        });

        return result;
    }

    private List getRows(NamedParameterStatement nps) {
        try {
            ResultSet rs = nps.executeQuery();
            Oracle10gRowSetDynaClass rsdc = new Oracle10gRowSetDynaClass(rs);

            List temp = new ArrayList();

            BasicDynaClass bdc = new BasicDynaClass("row", BasicDynaBean.class,
                    rsdc.getDynaProperties());

            for (Iterator<DynaBean> rows = rsdc.getRows().iterator(); rows
                    .hasNext();) {
                DynaBean oldRow = rows.next();
                DynaBean newRow = bdc.newInstance();
                PropertyUtils.copyProperties(newRow, oldRow);
                temp.add(newRow);
            }
            nps.close();
            return temp;
        } catch (Exception e) {
            throw new RuntimeException(e);
        }
    }

    /**
     * @see Object[] parameters = new Object[]{new Integer(0),new
     * String("CC_TMP_OMISMOR")}; Integer i = (Integer)executeFunction("{ ? =
     * call p_siit_functions.getNextVal(?) }" ,parameters);
     * @param cadena sql y arreglo de parametros
     * @return nothing,
     */
    public Object executeFunction(String sql, Object[] params)
            throws SQLException {

        final Object[] outPutFinal = new Object[1];
        final String sqlFinal = sql;
        final Object[] paramsFinal = params;

        sessionFactory.getCurrentSession().doWork(new Work() {
            public void execute(Connection conn) {

                CallableStatement cstmt = null;

                try {
                    cstmt = conn.prepareCall(sqlFinal);

                    if (paramsFinal != null) {
                        for (int i = 0; i < paramsFinal.length; i++) {
                            Object param = paramsFinal[i];
                            if (i == 0) {
                                cstmt.registerOutParameter(1,
                                        resolveType(param));
                            } else {

                                cstmt.setObject(i + 1, param,
                                        resolveType(param));
                            }
                        }
                    }
                    cstmt.execute();
                    Object obj = cstmt.getObject(1);
                    outPutFinal[0] = obj;
                    cstmt.close();
                } catch (SQLException e) {
                    try {
                        cstmt.close();
                    } catch (SQLException ex) {
                        throw new HibernateException(e);
                    }
                    throw new HibernateException(e);

                } finally {
                    cstmt = null;

                }

            }
        });

        return outPutFinal[0];
    }

    private int resolveType(Object param) {
        int sqlType = Types.VARCHAR;
        if (param == null) {
            sqlType = Types.NULL;
        } else {
            Class paramClass = param.getClass();
            if (param instanceof String) {
                sqlType = Types.VARCHAR;
            } else if (paramClass.equals(double.class)
                    || param instanceof Double) {
                sqlType = Types.DOUBLE;
            } else if (param instanceof BigDecimal) {
                sqlType = Types.NUMERIC;
            } else if (param instanceof Calendar
                    || param instanceof java.sql.Date) {
                sqlType = Types.DATE;
            } else if (paramClass.equals(int.class) || param instanceof Integer) {
                sqlType = Types.INTEGER;
            } else if (paramClass.equals(long.class) || param instanceof Long) {
                sqlType = Types.BIGINT;
            } else if (paramClass.equals(float.class) || param instanceof Float) {
                sqlType = Types.REAL;

            }
        }
        return sqlType;
    }

    /**
     *
     * @param lst Object list
     * @return the first object from the list, null if its an empty list
     */
    public Object getFirstFromList(List lst) {
        for (Iterator iter = lst.iterator(); iter.hasNext();) {
            return iter.next();
        }
        return null;
    }

    public List getAllOrderBy(String[] properties) {
        DetachedCriteria dc = DetachedCriteria.forClass(getClazz());

        for (int i = 0; i < properties.length; i++) {
            String prop = properties[i];
            dc.addOrder(Order.asc(prop));
        }

        return executeCriteria(dc);
    }

    public ClassMetadata getMetadata() {
        return sessionFactory.getClassMetadata(
                getClazz());
    }

    @SuppressWarnings("rawtypes")
    public Class getClazz() {
        return clazz;
    }

    @SuppressWarnings("rawtypes")
    public void setClazz(Class clazz) {
        this.clazz = clazz;
    }

    public SessionFactory getSessionFactory() {
        return sessionFactory;
    }

    public void setSessionFactory(SessionFactory sessionFactory) {
        this.sessionFactory = sessionFactory;
    }

    public JdbcHelper getJdbcHelper() {
        getSessionFactory().getCurrentSession().doWork(
                new Work() {
            public void execute(Connection connection) throws SQLException {
                jdbcHelper = new JdbcHelper(connection);
            }
        });
        return jdbcHelper;
    }

    public void merge(Object obj) {
        sessionFactory.getCurrentSession().merge(obj);

    }

    public Long getNextSequence(String sequenceName) {
        String sql = "{ ? = call pkg_siit_functions.getNextVal(?) }";
        Long seqValue = new Long(0);
        try {
            seqValue = (Long) executeFunction(sql, new Object[]{new Long(0),
                sequenceName});
        } catch (Exception e) {
            seqValue = null;
        }
        return seqValue;
    }

    public List<DynaBean> getListDynaBean(String sql, Object[] parameters) {
        final Object[] lstFinal = new Object[1];
        final String sqlFinal = sql;
        final Object[] parametersFinal = parameters;

        getSessionFactory().getCurrentSession().doWork(new Work() {
            public void execute(Connection con) throws SQLException {
                try {
                    JdbcHelper jdbcHelper = new JdbcHelper(con);

                    lstFinal[0] = jdbcHelper.getListDynaBean(sqlFinal,
                            parametersFinal);
                    jdbcHelper = null;

                } catch (Exception e) {
                    throw new HibernateException(e);
                }
            }
        });

        return (List) lstFinal[0];

    }

    public XmlResultSet getXmlResultSet(String sql, Object[] parameters) {
        final Object[] xmlResultSetFinal = new Object[1];
        final String sqlFinal = sql;
        final Object[] parametersFinal = parameters;

        sessionFactory.getCurrentSession().doWork(new Work() {
            public void execute(Connection con) throws SQLException {
                try {
                    JdbcHelper jdbcHelper = new JdbcHelper(con);
                    xmlResultSetFinal[0] = jdbcHelper.getXmlResultSet(sqlFinal, parametersFinal);
                } catch (Exception e) {
                    throw new HibernateException(e);

                }
            }
        });
        return (XmlResultSet) xmlResultSetFinal[0];

    }
}// class
